# -*- coding: utf-8 -*-
from datetime import datetime

import pymysql

from pymongo import MongoClient

mg = MongoClient('127.0.0.1:27017').bigbang
connection = pymysql.connect(host='127.0.0.1',
                             user='root',
                             password='123456',
                             db='local',
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)

print '用户id,渠道号,	登录ip,注册时间,流失天数,绑定手机号,充值金额,提现金额,当前余额,购彩金额,购彩中奖金额,电玩城参与金额,电玩城中奖金额'

for item in mg.user_stats.find().sort('updated_at', -1):
    if not item.get('recharge'):
        continue
    try:
        user_id = int(item['_id'])
    except ValueError:
        continue
    chn = item.get('chn', '')
    ip = item.get('ip', '')
    register_at = item.get('register_at')
    if register_at:
        register_at = register_at.strftime("%Y-%m-%d")
    else:
        register_at = '-'
    updated_at = item.get('updated_at')
    if updated_at:
        lose_days = (datetime.now() - updated_at).days
    else:
        lose_days = '-'
    with connection.cursor() as cursor:
        sql = "SELECT phone, balance FROM account WHERE id=%s;" % user_id
        cursor.execute(sql)
        result = cursor.fetchone()
        if result:
            phone = result.get('phone', '')
            balance = result.get('balance', 0)
        else:
            phone = '-'
            balance = 0
    recharge_amount = item.get('recharge').get('total', 0)

    if 100000 < recharge_amount <= 50000000:
    # if 100000 < recharge_amount:
        withdraw_amount = item.get('withdraw', {}).get('total', 0)
        lottery_bet_amount = item.get('pay', {}).get('total', 0)
        casino_bet_amount = item.get("lottery", {}).get("bet_amount", 0) + \
                            item.get("kfc", {}).get("bet_amount", 0) + \
                            item.get("fruit", {}).get("bet_amount", 0) + \
                            item.get("bull", {}).get("bet_amount", 0)
        lottery_win_amount = item.get('win', {}).get('total', 0)
        casino_win_amount = item.get("lottery", {}).get("win_amount", 0) + \
                            item.get("kfc", {}).get("award_amount", 0) + \
                            item.get("fruit", {}).get("win_amount", 0) + \
                            item.get("bull", {}).get("win_amount", 0)

        print('%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s' % (
            user_id, chn, ip, register_at, lose_days, phone,
            round(recharge_amount, 3),
            round(withdraw_amount, 3),
            round(balance, 3),
            round(lottery_bet_amount, 3),
            round(lottery_win_amount, 3),
            round(casino_bet_amount, 3),
            round(casino_win_amount, 3)))
